Database Example

FILE: Database.cln

Introduction

This example shows how the database objects work and shows an interaction with the calculation objects. These objects are called database objects because they have database like functions, but don't let that restrict you, they are objects like any other, use them wherever their function is useful.

Scenario

You are an employer with a list of employees, their date of birth, sales points scored and their sex. You want to award end of year bonuses. The rules for end of year bonuses are complex:

Only women and men born after 1960 are eligible.

Bonuses are given to the top three performers only

The bonus is dependent on age

It may not be fair, but it's the agreed bonus scheme, so how do we calculate this with CleanSheet?

The Sheet

The sheet below shows the way to calculate this. It splits into 3 sections.

The top section extracts the 3 top scorers that meet the requirements.

The middle section calculates the bonus for these people

The bottom section Displays and/or prints out the results

Working from the top and tracing along the pipes, click on each object and read what it does.


This is the table of all the employees, their scores, dates of birth and sex. It is a single Input Table Object. Notice that the headings in the first row are not part of the data, the Input Table has been set to remove these headings.


This is a Database Filter Object. It is set to allow through only those rows that have element 3 equal to FALSE. Element 3 is the element that contains TRUE if the person is male, or FALSE if the person is female. The result from this object is a table of FEMALE employees.


This is a Database Filter Object. It is set to allow through only those rows that have element 1 (the dates of birth) greater than 1st Jan 1960. The result from this object is a table that contains all employees (male and female) whose dates of birth are after this date.


This is a Database Merge object. It takes the table of female employees and table of all employees born after 1st Jan 1960 and merges them into one table. It is setup to join the two sets of rows together removing duplicates.

Since there are female employees who are born after 1st Jan 1960, their records appear in both lists, this object will remove one of those duplicates, so that each employee is present in only 1 row.

The result from this object is a table of all employees who meet the criteria for receiving a bonus.


This is a Database Sort object. The data coming into this object is the table of suitable employees. This object is set to sort the rows into descending order based on column 2.

CleanSheet columns start at column 0, hence column 2 is the scores each employee obtained.

The output from this object is a list of eligible employees sorted into descending order of scores.


This is an Database Extractor Object. The table fed into it contains the list of eligible employees sorted into descending order of scores. Hence to obtain the top 3 scorers, we need to extract the top 3 rows.

The extract object is set to take out the top three rows and return them as the result.


This is an Output Table, displaying the top 3 people who will receive bonuses. It is not really necessary for the calculation, but provides a useful mid-way check.


This is an Announcer Object. This takes the 3 top earners and feeds them, row by row into the calculation object. The calculation object then feeds them to a Reporter Object which reassembles the rows back into an array.

This allows us to use one calculation again and again on repeated data. If we decided that the top 10 scorers are to receive bonuses, we would not have to change this part of the sheet.


This is a Calculator Object, it converts a row containing a single persons record into a row containing their name and calculated bonus.

The Announcer Object sends a 1 dimensional array into this object that typically looks like this:

{"Somerset, T", 29/2/72, 100, TRUE}

The calculator has the equation {A[0],(today-A[1])/365*100} in it.

A[1]	Element 1 of Input A is the date of birth field from the array.

today-A[1]	Is the age of the person (today is the current date) in days. Subtracting one date from another gives the difference between them in days.

(today-A[1])/365*100	The bonus is £100 for each year of their age, this converts their age into the amount of bonus they will receive.

{A[0],.....}	A[0] is the name (element 0 of the array) of the person. The outer set of curly brackets group the name, together with the amount of bonus into a single array, e.g.,  {"Somerst, T" , 2148.21917}


This is a Report Object. It assembles the rows that come into it into a single array. The Reporter must be used with an Announcer. When the announcer has run out of things to announce, the corresponding reporter object stops reporting.


A table displaying the bonuses is shown here. This is an Output Table object.


This is a Hard Copy object. It prints the array data it receives to the printer.

This object will print every time the sheet is recalculated, but in order to stop it, a switch has been included. When the switch is set to B, then data can flow to the Hard Copy object, when the switch is set to A, the data flow is stopped.


This is a C to AB Switch. It has two states, A where the input connects to output A and B where the input connects to output B.

It is used here to stop the Hard Copy object printing. If the sheet is in Automatic recalculation mode, then every time something is changed, the Hard Copy object will print. By introducing this switch, the data is only allowed through to the hard copy object when the switch is in state B.

To print the finished table, switch this switch to B. If automatic recalculation is selected, then the table will be printed, if manual recalculation is selected, then click on the Manual Recalculation button.